Method, apparatus, and computer program product for implementing enhanced query governor functions

ABSTRACT

A method, apparatus and computer program product are provided for implementing enhanced query governor functions. Query execution includes first checking for a timeout value for a query. Responsive to identifying a timeout value for the query, an execution time for the query is reset and a monitor for each timeout value for the query is started. Then the execution of the query is started. The execution of predefined events is monitored during the execution of the query. The predefined events include a begin or end of processing of at least one of a trigger and a user defined function (UDF). Execution status of the query is periodically checked. Responsive to identifying the query is executing, checking for any expired timeout value is performed. The execution of the query is halted responsive to an identified expired timeout value.

FIELD OF THE INVENTION

The present invention relates generally to the database managementfield, and more particularly, relates to a method, apparatus andcomputer program product for implementing enhanced query governorfunctions.

DESCRIPTION OF THE RELATED ART

Databases are computerized information storage and retrieval systems.Databases are managed by systems and may take the form of relationaldatabases and hierarchical databases. A Relational Database ManagementSystem (RDBMS) is a database system that uses relational techniques forstoring and retrieving data. Relational databases are organized intotables consisting of rows (tuples) and columns of data. A relationaldatabase typically includes many tables, and each table includesmultiple rows and columns. The tables are conventionally stored indirect access storage devices (DASD), such as magnetic or optical diskdrives, for semi-permanent storage.

Relational Database Management System (RDBMS) software using aStructured Query Language (SQL) interface is well known in the art. TheSQL interface has evolved into a standard language for RDBMS softwareand has been adopted as such by both the American Nationals StandardOrganization (ANSI) and the International Standards Organization (ISO).

A database management system (DBMS) typically includes some form ofquery governor. Known query governors typically enable a databaseadministrator and user of the database to have queries time out if thequeries take too long. In this case queries are prevented from taking uptoo much system resources.

Current technology simply allows the database user to time out a querybased upon execution time but does take into account multiple aspects orthe breakdown of a query into executing components.

A need exists for a mechanism to enable the database user to be allowedto modify multiple query attributes including multiple executingcomponents of a query. It is desirable that a query can be broken downinto multiple query execution components, for example, data retrieval,trigger processing, and user defined function (UDF) processing, and witheach of these query execution components having an individual time outvalue.

SUMMARY OF THE INVENTION

A principal object of the present invention is to provide a method,apparatus and computer program product for implementing enhanced querygovernor functions. Other important objects of the present invention areto provide such method, apparatus and computer program product forimplementing enhanced query governor functions substantially withoutnegative effect and that overcome many of the disadvantages of prior artarrangements.

In brief, a method, apparatus and computer program product are providedfor implementing enhanced query governor functions. Query executionincludes first checking for a timeout value for a query. Responsive toidentifying a timeout value for the query, an execution time for thequery is reset and a monitor for each timeout value for the query isstarted. Then the execution of the query is started. The execution ofpredefined events is monitored during the execution of the query. Thepredefined events include a begin or end of processing of at least oneof a trigger and a user defined function (UDF). Execution status of thequery is periodically checked. Responsive to identifying the query isexecuting, checking for any expired timeout value is performed. Theexecution of the query is halted responsive to an identified expiredtimeout value.

In accordance with features of the invention, empirical data for triggerprocessing and UDF processing is used to determine whether in mostlikelihood that the query can finish within set timeout values for thetrigger and user defined function (UDF), and execution of the query isstarted only responsive to determining in most likelihood the query canfinish within the timeout values.

BRIEF DESCRIPTION OF THE DRAWINGS

The present invention together with the above and other objects andadvantages may best be understood from the following detaileddescription of the preferred embodiments of the invention illustrated inthe drawings, wherein:

FIG. 1 is a block diagram illustrating a computer system forimplementing methods for processing enhanced query governor functions inaccordance with the preferred embodiment;

FIGS. 2, 3, 4, and 5 are flow charts illustrating exemplary stepsperformed by the computer system of FIG. 1 for implementing enhancedquery governor functions in accordance with the preferred embodiment;

FIG. 6 is a diagram illustrating an exemplary data table for storing UDFand trigger data in accordance with the preferred embodiment; and

FIG. 7 is a block diagram illustrating a computer program product inaccordance with the preferred embodiment.

DETAILED DESCRIPTION OF THE PREFERRED EMBODIMENTS

Referring now to the drawings, in FIG. 1 there is shown a computersystem generally designated by the reference character 100 forimplementing methods for processing enhanced query governor functions inaccordance with the preferred embodiment. Computer system 100 includes aprocessor 102 coupled to a memory 104. Computer system 100 includes amass storage 106, such as a direct access storage device (DASD), adisplay 108, and a user input 110 coupled to the processor 102.

Computer system includes an operating system 112, a database managementsystem 114 including a query optimizer 116 and a query object 118. Thequery object 118 includes a number of methods or plans capable ofperforming specific operations relevant to management of the queryobject and the execution of a query represented by such an object.Database management system 114 includes a query governor program 120 ofthe preferred embodiment including a SQL processor program 122 and auser defined function (UDF) and trigger monitor program 124, and a UDFand trigger data table 126. Computer system includes a database 130stored in the mass storage 106, and a local area network (LAN) or widearea network (WAN) 132 that couples I/O devices 134, such as personalcomputers to the computer system 100. Computer system 100 is shown insimplified form sufficient for an understanding of the presentinvention.

In accordance with features of the preferred embodiment, query governorprogram 120 of the preferred embodiment includes the SQL processorprogram 122 and UDF and trigger monitor program 124. The query governorprograms 120, 122, 124 enable the database administrator and users ofthe database to modify query attributes and to monitor and maintainempirical data for implementing enhanced query governor functions of thepreferred embodiment. With the query governor programs 120, 122, 124,the components of a query are broken down into query execution, i.e.,data retrieval, trigger processing, and user defined function (UDF)processing. The database user is allowed to break down a query intomultiple executing components or individual pieces, each with individualtime out values. Empirical data for multiple query attributes, such ashow long UDFs and triggers take to execute, is maintained in accordancewith features of the preferred embodiment.

Advantages are provided by the enhanced query governor functions of thepreferred embodiment. For example, the database administrator or user isenabled to modify query attributes, such as turning off certain triggerprocessing and changing a web service UDF.

It should be understood that the present invention is not limited foruse with the illustrated computer system 100. The illustrated processorsystem 100 is not intended to imply architectural or functionallimitations. The present invention can be used with various hardwareimplementations and systems and various other internal hardware devices,for example, multiple main processors.

Various commercially available processors could be used for computersystem 100, for example, an AS/400 or iSeries computer systemmanufactured and sold by International Business Machines Corporation.

Referring to FIGS. 2, 3, 4, and 5 there are shown exemplary stepsperformed by the computer system 100 for implementing methods forprocessing enhanced query governor functions in accordance with thepreferred embodiment. The query governor program 120 including SQLprocessor program 122 and UDF and trigger monitor program 124 of thepreferred embodiment perform the exemplary steps for processing enhancedquery governor functions in accordance with the preferred embodiment.

Referring first to FIG. 2, there are shown exemplary steps of the SQLprocessor program 122 starting at a block 200 for handling SQL events.First, waiting for an event is provided as indicated in a block 202.When an event is received, checking to determine if the received eventis to modify query attributes as indicated in a decision block 204. Ifso, then a modify attributes routine is performed as indicated in ablock 206. The modify attributes routine is illustrated and describedwith respect to FIG. 4. Otherwise, if the received event is not tomodify query attributes, checking whether the event is an event toexecute a query is performed as indicated in a decision block 208. Ifso, then an execute query routine is performed as indicated in a block210. The execute query routine is illustrated and described with respectto FIG. 3. Otherwise, if not an event to execute a query, then any otherknown SQL event known in the art is processed as indicated in a block212. Then the sequential operations return to block 202 to wait foranother event.

Referring now to FIG. 3, there are shown exemplary steps of the executequery routine starting at a block 300 for handling execution of queries.Before starting to process the query, checking to determine if anytimeouts exist for this query is performed as indicated in a decisionblock 301. Any timeouts for this query are set through changing thequery attributes using the modify attributes routine of FIG. 4. If notimeouts exist, then the query is executed as known in the art asindicated in a block 302. If a timeout exists, then checking todetermine based on empirical data whether in most likelihood that thequery can finish within the timeout values is performed as indicated ina decision block 303. If in most likelihood the query will not finishwithin the timeout values, then the query is not even started and thesequential steps end or exit as indicated in a block 304. Otherwise ifdetermined that the query can finish within the timeout values, then theexecution time is reset for each timeout as indicated in a block 306 andmonitors are set as indicated in a block 308. A monitor in this case isone that will track processing time of a user defined function (UDF)and/or a trigger. The monitor or monitors track the timeout valuespreviously set.

Then the execution of the query is started as indicated in a block 310.After starting the execution of the query in a separate thread, thismain thread then waits a predetermined period of time as indicated in ablock 312. This predetermined period of time or time value is asub-factor of the timeout value. This means if a limit of 10 seconds isset for UDF processing, then the predetermined period of time for thewait limit will be a division of 10 seconds, such that a wake up is setfor checking if the query is still executing as indicated in a decisionblock 314. If the query is not executing, then the sequential steps endor exit as indicated in a block 316 because the query is done executing.Otherwise, checking whether the time limit has expired is performed asindicated in a decision block 318. If any of the timeouts has beenreached, then execution of the query is halted as indicated in a block320 and a return code is set as indicated in a block 322. Then thesequential steps end or exit as indicated in a block 324. Otherwise ifno time limit has expired at decision block 318, then the sequentialsteps return to block 312 to wait for the predetermined period of time.

Referring now to FIG. 4, there are shown exemplary steps of the modifyattributes routine starting at a block 400 to set query attributes.First checking to determine if a monitor is being requested is performedas indicated in a decision block 402. If so, then the monitor is set fora timeout as indicated in a block 404. Then as indicated in a block 406,the rest of the query attributes are changed as known in the art. Thenthe sequential steps end or exit as indicated in a block 408. Otherwiseif a monitor is not being requested, then the rest of the queryattributes are changed as known in the art at block 406 and thesequential steps exit at block 408.

As described with respect to block 406 in FIG. 4, enhanced querygovernor functionality is being added to the known or existing art ofchanging query attributes. Changing query attributes generally allowsfor various other query functionalities; that-is used in accordance withfeatures of the preferred embodiment to set timeout values for UDFprocessing and trigger processing.

Referring now to FIG. 5, there are shown exemplary steps of the UDF andtrigger monitor program 124 starting at a block 500 for monitoring theexecution of each UDF and each trigger such that empirical data can bemaintained on how long the UDFs and triggers take to execute. First,waiting for an event is provided as indicated in a block 502. When anevent is received, checking to determine if the received event is atrigger event begin or end as indicated in a decision block 504. If so,then the trigger event and start or stop time is recorded as indicatedin a block 506. Otherwise when the received event is not a triggerevent, checking to determine if the received event is a UDF event beginor end as indicated in a decision block 508. If so, then the UDF eventand start or stop time is recorded as indicated in a block 510.Otherwise, if not a trigger or UDF event begin or end, then any otherknown SQL event known in the art is processed as indicated in a block512. Then the sequential operations return to block 502 to wait foranother event.

Referring now to FIG. 6, there is shown an exemplary data table 126 forstoring UDF and trigger data in accordance with the preferredembodiment. The UDF and trigger data table 126 stores a start time 602,a stop time 604, and an event 606, either a UDF event or a triggerevent. The UDF and trigger data table 126 contains the start and stoptimes 602, 604 for UDFs and triggers 606 that is kept up to date by theprocessing performed as shown in the flow chart of FIG. 5.

Referring now to FIG. 7, an article of manufacture or a computer programproduct 700 of the invention is illustrated. The computer programproduct 700 includes a recording medium 702, such as, a floppy disk, ahigh capacity read only memory in the form of an optically read compactdisk or CD-ROM, a tape, a transmission type media such as a digital oranalog communications link, or a similar computer program product.Recording medium 702 stores program means 704, 706, 708, 710 on themedium 702 for carrying out the methods for implementing enhanced querygovernor functions of the preferred embodiment in the system 100 of FIG.1.

A sequence of program instructions or a logical assembly of one or moreinterrelated modules defined by the recorded program means 704, 706,708, 710, direct the computer system 100 for processing enhanced querygovernor functions of the preferred embodiment.

While the present invention has been described with reference to thedetails of the embodiments of the invention shown in the drawing, thesedetails are not intended to limit the scope of the invention as claimedin the appended claims.

1. A method for implementing enhanced query governor functionscomprising the steps of: checking for a timeout value for a query,responsive to identifying a timeout value for the query, resetting anexecution time for the query; starting a monitor for each timeout valuefor the query; starting the execution of the query; monitoring theexecution of predefined events during the execution of the query; saidpredefined events including a begin or end of processing of at least oneof a trigger and a user defined function (UDF); periodically checkingexecution status of the query; responsive to identifying the query isexecuting, checking for any expired timeout value; and halting theexecution of the query responsive to an identified expired timeoutvalue.
 2. A method for implementing enhanced query governor functions asrecited in claim 1 wherein the step of starting monitors for eachtimeout value for the query includes the step of starting monitors foreach UDF and trigger.
 3. A method for implementing enhanced querygovernor functions as recited in claim 1 wherein the step of monitoringthe execution of said predefined events includes the step of recordingeach said trigger event start time and stop time.
 4. A method forimplementing enhanced query governor functions as recited in claim 1wherein the step of monitoring the execution of said predefined eventsincludes the step of recording each said user defined function (UDF)start time and stop time.
 5. A method for implementing enhanced querygovernor functions as recited in claim 1 wherein the step of monitoringthe execution of said predefined events includes the step of recordingempirical data for said trigger, and said user defined function (UDF).6. A method for implementing enhanced query governor functions asrecited in claim 5 includes the step of checking to determine based uponsaid recorded empirical data whether in most likelihood that the querycan finish within timeout values for said trigger and said user definedfunction (UDF), and responsive-to determining in most-likelihood thequery will not finish within said timeout values, execution of the queryis not started.
 7. A method for implementing enhanced query governorfunctions as recited in claim 1 includes the step responsive to haltingthe execution of the query of, setting return code values for saididentified expired timeout value for processing either said UDF or saidtrigger.
 8. A method for implementing enhanced query governor functionsas recited in claim 1 includes the steps of monitoring events, andresponsive to an event to modify attributes, performing a modifyattributes routine.
 9. A method for implementing enhanced query governorfunctions as recited in claim 8 wherein said modify attributes routineincludes the steps responsive to a monitor being requested, setting atimeout for the monitor.
 10. A method for implementing enhanced querygovernor functions as recited in claim 8 includes the steps responsiveto an event to execute query, performing an execute query routine. 11.Apparatus for implementing enhanced query governor functions comprising:a query governor program including a SQL processor program, said SQLprocessor program for monitoring events, and said SQL processor programresponsive to an event to modify attributes, performing a modifyattributes routine; and responsive to an event to execute query,performing an execute query routine; said modify attributes routineincluding the steps responsive to a monitor being requested, setting atimeout for the monitor; and said query governor program including auser defined function (UDF) and trigger monitor program; said UDF andtrigger monitor program for monitoring the execution of predefinedevents during the execution of the query; said predefined eventsincluding a begin or end of processing of at least one of a trigger anda user defined function (UDF).
 12. Apparatus for implementing enhancedquery governor functions as recited in claim 11 wherein said SQLprocessor program responsive to said event to execute query performingsaid execute query routine includes the steps of checking to determinebased upon said recorded empirical data whether in most likelihood thatthe query can finish within timeout values for said trigger and saiduser defined function (UDF), and only responsive to determining in mostlikelihood the query can finish within said timeout values, startingexecution of the query.
 13. Apparatus for implementing enhanced querygovernor functions as recited in claim 11 wherein said SQL processorprogram responsive to said event to execute query performing saidexecute query routine includes the steps of identifying an expiredtimeout value for said trigger or said UDF, halting the execution of thequery.
 14. A computer program product for implementing enhanced querygovernor functions in a computer system, said computer program productincluding instructions executed by the computer system to cause thecomputer system to perform the steps of: responsive to identifying anexecute query event, checking for a timeout value for a query,responsive to identifying a timeout value for the query, resetting anexecution time for the query; starting a monitor for each timeout valuefor the query; starting the execution of the query; monitoring theexecution of predefined events during the execution of the query; saidpredefined events including a begin or end of processing of at least oneof a trigger and a user defined function (UDF); periodically checkingexecution status of the query; responsive to identifying the query isexecuting, checking for any expired timeout value; and halting theexecution of the query responsive to an identified expired timeoutvalue.
 15. A computer program product for implementing enhanced querygovernor functions as recited in claim 14 wherein said instructionsfurther cause the computer system to perform the steps of monitoringevents, and responsive to an event to modify attributes, performing amodify attributes routine.
 16. A computer program product forimplementing enhanced query governor functions as recited in claim 15wherein said modify attributes routine includes the steps responsive toa monitor being requested, setting a timeout for the monitor.
 17. Acomputer program product for implementing enhanced query governorfunctions as recited in claim 14 wherein the step of monitoring theexecution of said predefined events includes the step of recordingempirical data for said trigger, and said user defined function (UDF).18. A computer program product for implementing enhanced query governorfunctions as recited in claim 17 includes the steps of checking todetermine based upon said recorded empirical data whether in mostlikelihood that the query can finish within timeout values for saidtrigger and said user defined function (UDF), and only responsive todetermining in most likelihood the query can finish within said timeoutvalues, starting execution of the query.